﻿using DataAccess.Exceptions;
using Model;
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DataAccess
{
    public static class TeacherDataAccess
    {
        public static string ConnString = Conexion.DataSource;

        public static Collection<Teacher> GetAllTeachers()
        {
            SqlConnection co = null;
            Collection<Teacher> teacherList = null;
            try
            {
                teacherList = new Collection<Teacher>();
                co = new SqlConnection(ConnString);
                SqlCommand cmd = co.CreateCommand();
                cmd.CommandText = "SELECT * FROM [Docente]";
                co.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Teacher teacherResult = new Teacher();

                    teacherResult.CI = Convert.ToInt32(reader["ci"].ToString());
                    teacherResult.Name = reader["nombre"].ToString();
                    teacherResult.lastNameFather = reader["apePat"].ToString();
                    teacherResult.lastNameMother = reader["apeMat"].ToString();
                    teacherResult.sex = reader["sexo"].ToString();
                    teacherResult.dateBorn = Convert.ToDateTime(reader["fechaNac"].ToString());
                    teacherResult.whereIsBorn = reader["lugarNac"].ToString();
                    teacherResult.expidedIn = reader["expedidoEn"].ToString();
                    teacherResult.professionAero = reader["licenciaAero"].ToString();
                    teacherResult.profession = reader["profesion"].ToString();
                    teacherResult.liveIn = reader["domicilio"].ToString();
                    teacherResult.zoneLiving = reader["zona"].ToString();
                    teacherResult.phone = reader["telefono"].ToString();
                    teacherResult.eMail = reader["email"].ToString();
                    teacherResult.profession = reader["profesion"].ToString();                                                            

                    teacherList.Add(teacherResult);
                }
                return (teacherList);
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error al obtener al docente", ex);
            }
            finally
            {
                if (co != null)
                {
                    co.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }

        public static Teacher getTeacherByCI(long p)
        {
            SqlConnection co = null;
            Teacher result = null;
            try
            {                
                co = new SqlConnection(ConnString);
                SqlCommand cmd = co.CreateCommand();
                cmd.CommandText = "SELECT * FROM [Docente]";
                co.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    result = new Teacher();

                    result.CI = Convert.ToInt32(reader["ci"].ToString());
                    result.Name = reader["nombre"].ToString();
                    result.lastNameFather = reader["apePat"].ToString();
                    result.lastNameMother = reader["apeMat"].ToString();
                    result.sex = reader["sexo"].ToString();
                    result.dateBorn = Convert.ToDateTime(reader["fechaNac"].ToString());
                    result.whereIsBorn = reader["lugarNac"].ToString();
                    result.expidedIn = reader["expedidoEn"].ToString();
                    result.professionAero = reader["licenciaAero"].ToString();
                    result.profession = reader["profesion"].ToString();
                    result.liveIn = reader["domicilio"].ToString();
                    result.zoneLiving = reader["zona"].ToString();
                    result.phone = reader["telefono"].ToString();
                    result.eMail = reader["email"].ToString();
                    result.profession = reader["profesion"].ToString();
                }
                return (result);
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error al obtener al docente", ex);
            }
            finally
            {
                if (co != null)
                {
                    co.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }

        internal static Collection<Teacher> getTeacherLikeByCI(string filterCITeacher)
        {
            SqlConnection co = null;
            Collection<Teacher> teacherList = null;
            try
            {
                teacherList = new Collection<Teacher>();
                co = new SqlConnection(ConnString);
                SqlCommand cmd = co.CreateCommand();
                cmd.CommandText = "SELECT * FROM [Docente] WHERE ci LIKE @V_ci";
                cmd.Parameters.AddWithValue("V_ci", filterCITeacher + "%");

                co.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Teacher teacherResult = new Teacher();

                    teacherResult.CI = Convert.ToInt32(reader["ci"].ToString());
                    teacherResult.Name = reader["nombre"].ToString();
                    teacherResult.lastNameFather = reader["apePat"].ToString();
                    teacherResult.lastNameMother = reader["apeMat"].ToString();
                    teacherResult.sex = reader["sexo"].ToString();
                    teacherResult.dateBorn = Convert.ToDateTime(reader["fechaNac"].ToString());
                    teacherResult.whereIsBorn = reader["lugarNac"].ToString();
                    teacherResult.expidedIn = reader["expedidoEn"].ToString();
                    teacherResult.professionAero = reader["licenciaAero"].ToString();
                    teacherResult.profession = reader["profesion"].ToString();
                    teacherResult.liveIn = reader["domicilio"].ToString();
                    teacherResult.zoneLiving = reader["zona"].ToString();
                    teacherResult.phone = reader["telefono"].ToString();
                    teacherResult.eMail = reader["email"].ToString();
                    teacherResult.profession = reader["profesion"].ToString();

                    teacherList.Add(teacherResult);
                }
                return (teacherList);
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error al obtener al docente", ex);
            }
            finally
            {
                if (co != null)
                {
                    co.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }
    }
}
